package com.news.dao.impl;

import com.news.dao.CmsArticleDao;
import com.news.entity.Article;
import com.news.utils.DateUtils;
import com.news.utils.JdbcUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author:weiming
 * @Date:2021/3/28
 */
public class CmsArticleDaoImpl implements CmsArticleDao {

    private static final Connection CONNECTION = JdbcUtils.getConnection();


    @Override
    public List<Article> getAllArticleInfo(int pageNum, int pageSize){
        List<Article> list = new ArrayList<>();
        String sql = "SELECT a.id,title, author, is_deleted, is_show, text_id,text,img_url,category_name,category_id,create_time FROM  cms_article as a,(SELECT id FROM cms_article) as b ,cms_article_text as c \n" +
                "where a.id = b.id and a.text_id = c.id and a.is_deleted = 0 and is_check = 1 order by a.id desc limit ?, ?   ";
        try {
            PreparedStatement pst = CONNECTION.prepareStatement(sql);
            pst.setInt(1, pageNum);
            pst.setInt(2, pageSize);
            ResultSet resultSet = pst.executeQuery();
            while(resultSet.next()){
                Article article = new Article();
                article.setId(resultSet.getInt(1));
                article.setTitle(resultSet.getString(2));
                article.setAuthor(resultSet.getString(3));
                article.setIsDeleted(resultSet.getInt(4));
                article.setIsShow(resultSet.getInt(5));
                article.setTextId(resultSet.getInt(6));
                article.setText(resultSet.getString(7));
                article.setImgUrl(resultSet.getString(8));
                article.setCategoryName(resultSet.getString(9));
                article.setCategoryId(resultSet.getInt(10));
                // TODO 日期转换
                Date sqlDate = resultSet.getDate(11);
                long time = sqlDate.getTime();
                java.util.Date date = new java.util.Date(time);
                article.setCreateTime(date);
                article.setCreateTimeStr(DateUtils.sqlDateToString(sqlDate));
                list.add(article);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }


    @Override
    public int add(Article article) {
        // TODO 日期
        String sql = "INSERT INTO cms_article (title, author, is_deleted, is_show, text_id, img_url, category_name, category_id, create_time, is_check, editor_id, role)" +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            CONNECTION.setAutoCommit(false);
            int textId = getTextId(article.getText());
            article.setTextId(textId);
            PreparedStatement pst = CONNECTION.prepareStatement(sql);
            pst.setString(1, article.getTitle());
            pst.setString(2, article.getAuthor());
            pst.setInt(3, article.getIsDeleted());
            pst.setInt(4, article.getIsShow());
            pst.setInt(5, article.getTextId());
            pst.setString(6, article.getImgUrl());
            pst.setString(7, article.getCategoryName());
            pst.setInt(8, article.getCategoryId());
            pst.setDate(9, DateUtils.toSqlDate(new java.util.Date()));
            pst.setInt(10, article.getIsCheck());
            pst.setInt(11, article.getEditorId());
            pst.setInt(12, article.getRole());
            pst.executeUpdate();
            CONNECTION.commit();
            CONNECTION.setAutoCommit(true);
            return 1;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    private int getTextId(String text) {
        int textId = 0;
        PreparedStatement pst = null;
        try {
            pst = CONNECTION.prepareStatement("INSERT INTO cms_article_text (text) VALUE (?)");
            pst.setString(1, text);
            int result = pst.executeUpdate();
            if (result == 1){
                pst = CONNECTION.prepareStatement("SELECT DISTINCT LAST_INSERT_ID() from cms_article_text");
            }
            ResultSet resultSet = pst.executeQuery();
            while (resultSet.next()){
                textId = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return textId;
    }

    @Override
    public int update(Article article) {
        int result = 0 ,result2 = 0;
        String sql = "UPDATE cms_article SET title = ?, author = ?, is_deleted = ?, is_show = ?, category_name = ?, category_id=?, img_url = ? WHERE id = ?";
        String sql2 = "UPDATE cms_article_text SET text = ? WHERE id = ?";
        try {
            CONNECTION.setAutoCommit(false);
            PreparedStatement pst = null;
            pst= CONNECTION.prepareStatement(sql);
            pst.setString(1, article.getTitle());
            pst.setString(2, article.getAuthor());
            pst.setInt(3, article.getIsDeleted());
            pst.setInt(4, article.getIsShow());
            pst.setString(5, article.getCategoryName());
            pst.setInt(6, article.getCategoryId());
            pst.setString(7, article.getImgUrl());
            pst.setInt(8, article.getId()); ;
            result = pst.executeUpdate();
            pst.close();// 需要关闭否则报空指针
            pst= CONNECTION.prepareStatement(sql2);
            pst.setString(1, article.getText());
            pst.setInt(2, article.getTextId());
            result2 = pst.executeUpdate();
            CONNECTION.commit();
            CONNECTION.setAutoCommit(true);
            if (result == 1 && result2 == 1){
                return result;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(Integer newsId) {
        int result = 0;
        String sql = "UPDATE cms_article SET is_deleted = ? WHERE id = ?";
        try {
            PreparedStatement pst =  CONNECTION.prepareStatement(sql);
            if (pst != null){
                pst.setInt(1, 1);
                pst.setInt(2, newsId);
            }
            result = pst.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public Article getArticleById(int id) {
        String sql = "SELECT b.id, title, author, is_deleted, is_show, text_id, img_url,create_time,b.text "  +
                "FROM cms_article as a inner join cms_article_text as b on a.text_id = b.id  where a.id = ?";
        try {

            PreparedStatement pst = CONNECTION.prepareStatement(sql);
            Article article = new Article();
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            int count = 0;
            while(rs.next()){{
                article.setId( rs.getInt(1));
                article.setTitle( rs.getString(2));
                article.setAuthor(rs.getString(3));
                article.setIsDeleted(rs.getInt(4));
                article.setIsShow( rs.getInt(5));
                article.setTextId( rs.getInt(6));
                article.setImgUrl(rs.getString(7));
                article.setCreateTimeStr(DateUtils.sqlDateToString(rs.getDate(8)));
                article.setText(rs.getString(9));
                count++;
            }}
            rs.close();
            pst.close();
            if (count != 0){
                return  article;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }


    @Override
    public Map<String,Object> getArticleListByCatelogyId(int catelogyId) {
        Map<String,Object> map = new HashMap<>(2);


        String sql = "SELECT a.id,title,create_time,category_name FROM cms_article as a inner join cms_article_text as b on a.text_id = b.id  where a.category_id = ? and is_show = 1 and is_deleted = 0 and is_check = 1";
        List<Article> articleList = null;
        try {
            CONNECTION.setAutoCommit(false);
            PreparedStatement pst = CONNECTION.prepareStatement(sql);
            pst.setInt(1, catelogyId);
            ResultSet resultSet = pst.executeQuery();
            articleList = new ArrayList<>();
            while (resultSet.next()){
                Article article = new Article();
                article.setId(resultSet.getInt(1));
                article.setTitle(resultSet.getString(2));
                article.setCreateTimeStr(DateUtils.sqlDateToString(resultSet.getDate(3)));
                article.setCategoryName(resultSet.getString(4));
                articleList.add(article);
            }
            map.put("articleList",articleList);
            pst.close();
            String sql2 = "SELECT name FROM cms_category WHERE id = ?";
            pst = CONNECTION.prepareStatement(sql2);
            pst.setInt(1, catelogyId);
            ResultSet rs2 = pst.executeQuery();
            String categoryName = new String();
            CONNECTION.setAutoCommit(false);
            CONNECTION.commit();
            while (rs2.next()){
                categoryName = rs2.getString(1);
            }
            map.put("categoryName",categoryName);


            return  map;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return map;
    }

    @Override
    public List getAllArticleByUser(int pageNum, int pageSize) {
        List<Article> list = new ArrayList<>();
        String sql = "SELECT a.id,title, author, is_deleted, is_show, text_id,text,img_url,category_name,category_id,create_time,is_check FROM  cms_article as a,(SELECT id FROM cms_article) as b ,cms_article_text as c " +
                "where a.id = b.id and a.text_id = c.id and  role = 1 order by a.id limit ?, ? ";
        try {
            PreparedStatement pst = CONNECTION.prepareStatement(sql);
            pst.setInt(1, pageNum);
            pst.setInt(2, pageSize);
            ResultSet resultSet = pst.executeQuery();
            while(resultSet.next()){
                Article article = new Article();
                article.setId(resultSet.getInt(1));
                article.setTitle(resultSet.getString(2));
                article.setAuthor(resultSet.getString(3));
                article.setIsDeleted(resultSet.getInt(4));
                article.setIsShow(resultSet.getInt(5));
                article.setTextId(resultSet.getInt(6));
                article.setText(resultSet.getString(7));
                article.setImgUrl(resultSet.getString(8));
                article.setCategoryName(resultSet.getString(9));
                article.setCategoryId(resultSet.getInt(10));
                Date sqlDate = resultSet.getDate(11);
                article.setCreateTime(DateUtils.toUtilDate(sqlDate));
                article.setCreateTimeStr(DateUtils.sqlDateToString(sqlDate));
                article.setIsCheck(resultSet.getInt(12));
                list.add(article);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    @Override
    public int changeCheck(Article article) {
        String sql = "UPDATE cms_article SET is_check = ? WHERE id = ?";
        int result = 0;
        try {
            PreparedStatement pst = CONNECTION.prepareStatement(sql);
            pst.setInt(1, article.getIsCheck());
            pst.setInt(2, article.getId());
            result = pst.executeUpdate();
            if (result == 1){
                return  1;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public Map<String, Object> getTotalData() {
        Map<String,Object> map = new HashMap<>(3);
        String[] sqlArr = {"SELECT 	count(*) as article from cms_article WHERE is_deleted = 0",
                "SELECT  count(*) as comment from cms_comment where is_show = 1",
                "SELECT  count(*) as user from ums_user "};
       String[] key ={"article","comment","user"};
        try {
            CONNECTION.setAutoCommit(false);

            int i = 0;
            for (String sql : sqlArr) {
                PreparedStatement pst = null;
                pst = CONNECTION.prepareStatement(sql);
                ResultSet resultSet = pst.executeQuery();
                while (resultSet.next()){
                    int result = resultSet.getInt(1);
                    map.put(key[i],result);
                }
                i++;
                pst.close();
                resultSet.close();
            }
            CONNECTION.commit();
            CONNECTION.setAutoCommit(true);
            return map;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return map;
    }

    @Override
    public int uploadFile(Map<String,Object> file) {
        String sql = "insert into cms_file (file_name,file_url) value (?,?)";
        int result = 0;
        try {
            PreparedStatement pst = CONNECTION.prepareStatement(sql);
            pst.setString(1, (String) file.get("title"));
            pst.setString(2, (String) file.get("src"));
            result = pst.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public List<Map<String, Object>> getSearchResult(String title) {

        String sql =   "select id,title from cms_article WHERE is_deleted = 0 and is_check = 1 and is_show = 1 and  title LIKE ? ";
        List<Map<String,Object>> resultList = new ArrayList<>();
        try {
            PreparedStatement pst = CONNECTION.prepareStatement(sql);
            pst.setString(1,"%"+title+"%");
            ResultSet rs = pst.executeQuery();
            while (rs.next()){
                HashMap map = new HashMap(2);
                int id = rs.getInt("id");
                String  rsTitle = rs.getString("title");
                map.put("id",id);
                map.put("title",rsTitle);
                resultList.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return resultList;
    }
}
